-- 1. 定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额
select * from AccountInfo
select * from BankCard
select * from CardExchange
go
create proc proc_GetMin
as
begin
	select CardNo 银行卡号, RealName 姓名,CardMoney 账户余额 from AccountInfo join BankCard on BankCard.AccountId=AccountInfo.AccountId
	where CardMoney =(select min(CardMoney) from BankCard)
end
go
exec proc_GetMin
drop procedure proc_GetMin
-- 2. 模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作
create proc proc_GeiMoneyBank
@cardNo varchar(30),
@MoneyInBank money
as
begin
	update BankCard set CardMoney+=@MoneyInBank where CardNo=@cardNo
	insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	values (@cardNo,@MoneyInBank,0,GETDATE())
end
go
exec proc_GeiMoneyBank '6225125478544587',3000
drop  proc proc_GeiMoneyBank
-- 3. 模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1
go
create proc proc_GeiMoneyBank
@cardNo varchar(30),
@MoneyOutBank money
as
begin
	update BankCard set CardMoney-=@MoneyOutBank where CardNo=@cardNo
	if (@@ERROR!=0)
	return -1
	insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	values (@cardNo,@MoneyOutBank,0,GETDATE())
	return 1
end
go
declare @MoneyOutBank int 
exec @MoneyOutBank = proc_GeiMoneyBank '6225547854125656',1000
print @MoneyOutBank
drop proc proc_GeiMoneyBank
-- 4. **查询出某时间段的银行存取款信息以及存款总金额**，取款总金额，传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。
go
create proc proc_Get 
    @startTime smalldatetime,  
    @endTime smalldatetime,    
    @SumIn money output,     
    @SumOut money output    
as
begin
 select @SumIn =sum(MoneyInBank),@SumOut=sum(MoneyOutBank)from CardExchange
 where ExchangeTime between @startTime and @endTime
 select * from CardExchange 
where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59'
end
go
declare @SumIn money     
declare @SumOut money   
exec proc_Get '2022-09-20','2022-09-20',@SumIn output,@SumOut output
drop proc proc_Get
-- 5. **密码升级**，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码
-- (提示：随机生成 0-9 的整数： float(rand()*10))  rand():随机生成0.0-1.0的小数 float:向下取整）
go
create proc proc_PwdUpgrade
@cardno nvarchar(20),
@pwd nvarchar(20) output
as
begin
    if not exists(select * from BankCard where CardNo=@cardno and CardPwd=@pwd)
        set @pwd = ''
    else
    begin
        if len(@pwd) < 8
        begin
            declare @len int = 8- len(@pwd)
            declare @i int = 1
            while @i <= @len
            begin
                
                set @pwd = @pwd + cast(FLOOR(RAND()*10) as varchar(1))
                set @i = @i+1
            end
            update BankCard set CardPwd = @pwd where CardNo=@cardno
        end
    end
end
go
declare @pwd nvarchar(20) = '123456'
exec proc_PwdUpgrade '6225547854125656',@pwd output
select @pwd
drop proc proc_PwdUpgrade
